// .........................................................................
// Title: consolidate_ratings.do
//
// Builds consolidated yearly panel of bond ratings by combining data from
// multiple rating agenceies and corresponding sources
// .........................................................................

* --------------------------------------
* Consolidate S&P and Moody's ratings
* --------------------------------------

* merged rating classifications: security-level
use "$tmp/wrds_bond_ratings_y", clear
keep cusip year rating*
mmerge cusip year using "$tmp/moodys/cusip_ratings_long_simplified_y"
mmerge cusip year using "$tmp/ciq_clean_ratings"
drop _merge

gen moodys_rating_converted = moodys_rating

replace moodys_rating_converted = "AAA" if moodys_rating_converted == "Aaa"
replace moodys_rating_converted = "AA+" if moodys_rating_converted == "Aa1"
replace moodys_rating_converted = "AA" if moodys_rating_converted == "Aa2"
replace moodys_rating_converted = "AA" if moodys_rating_converted == "Aa"
replace moodys_rating_converted = "AA-" if moodys_rating_converted == "Aa3"
replace moodys_rating_converted = "A+" if moodys_rating_converted == "A1"
replace moodys_rating_converted = "A" if moodys_rating_converted == "A2"
replace moodys_rating_converted = "A-" if moodys_rating_converted == "A3"

replace moodys_rating_converted = "BBB+" if moodys_rating_converted == "Baa1"
replace moodys_rating_converted = "BBB" if moodys_rating_converted == "Baa2"
replace moodys_rating_converted = "BBB" if moodys_rating_converted == "Baa"
replace moodys_rating_converted = "BBB-" if moodys_rating_converted == "Baa3"
replace moodys_rating_converted = "BB+" if moodys_rating_converted == "Ba1"
replace moodys_rating_converted = "BB" if moodys_rating_converted == "Ba2"
replace moodys_rating_converted = "BB" if moodys_rating_converted == "Ba"
replace moodys_rating_converted = "BB-" if moodys_rating_converted == "Ba3"
replace moodys_rating_converted = "B+" if moodys_rating_converted == "B1"
replace moodys_rating_converted = "B" if moodys_rating_converted == "B2"
replace moodys_rating_converted = "B-" if moodys_rating_converted == "B3"

replace moodys_rating_converted = "CCC+" if moodys_rating_converted == "Caa1"
replace moodys_rating_converted = "CCC" if moodys_rating_converted == "Caa2"
replace moodys_rating_converted = "CCC" if moodys_rating_converted == "Caa"
replace moodys_rating_converted = "CCC-" if moodys_rating_converted == "Caa3"

replace moodys_rating_converted = "D" if moodys_rating_converted == "C"
replace moodys_rating_converted = "D" if moodys_rating_converted == "Ca"
replace moodys_rating_converted = "D" if moodys_rating_converted == "SG"

replace moodys_rating_converted = "" if strpos(moodys_rating_converted, "MIG")
replace moodys_rating_converted = "" if strpos(moodys_rating_converted, "LGD")
replace moodys_rating_converted = "" if strpos(moodys_rating_converted, "P-")
replace moodys_rating_converted = "" if inlist(moodys_rating_converted, "GB1", "NP")

gen moodys_rating_converted_broad = moodys_rating_converted
replace moodys_rating_converted_broad = subinstr(moodys_rating_converted_broad, "+", "", .)
replace moodys_rating_converted_broad = subinstr(moodys_rating_converted_broad, "-", "", .)
replace rating_cat_pm = moodys_rating_converted if missing(rating_cat_pm)
replace rating_cat = moodys_rating_converted_broad if missing(rating_cat)
replace rating_cat_pm = iq_issue_rating_pm if missing(rating_cat_pm)
replace rating_cat = iq_issue_rating if missing(rating_cat)
replace rating_cat_pm = rating_cat if missing(rating_cat_pm)

drop if missing(rating_cat_pm)
drop moodys* iq_*

* fill in yearly panel
drop if missing(cusip)
egen _cusip = group(cusip)
gsort _cusip year
xtset _cusip year
tsfill
by _cusip: carryforward rating_cat rating_cat_pm, replace
drop _cusip

save "$tmp/issue_ratings_merged_sp_moodys_y", replace
